using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace QuanLyKhachSan
{
    public class LoaiPhongDAO
    {
        // Them mot loai phong
        public static bool Insert(LoaiPhongDTO loaiPhongdto)
        {
            string sql = "INSERT INTO LoaiPhong  VALUES (N'" + loaiPhongdto.TenLoaiPhong + "'," + loaiPhongdto.DonGia + ",0)";
            DataAccessHelper s = new SqlDataAccessHelper();

            int kq=s.ExecuteNonQuery1(sql);
            if (kq < 0)
                return false;
            return true;
        }

        // Xoa mot doi tuong loai phong
        public static bool Delete(int maLoaiPhong)
        {
            string sqlSelect = "Select maLoaiPhong from Phong where maLoaiPhong=" + maLoaiPhong;
            DataAccessHelper s = new SqlDataAccessHelper();
            DataTable dt = s.ExecuteQuery(sqlSelect);

            string sql = "UPDATE LoaiPhong SET danhDau=1 WHERE maLoaiPhong=" + maLoaiPhong;
            if (dt.Rows.Count <= 0)//khong co khoa ngoai tham chieu toi maLoaiPhong thi xoa di,nguoc lai thi danh dau =1 
            {
                sql = "DELETE from LoaiPhong WHERE maLoaiPhong ="+ maLoaiPhong;
            }                     

            int kq = s.ExecuteNonQuery1(sql);
            if (kq < 0)
                return false;
            return true;
        }
        
        // Cap nhat mot doi tuong loai phong
        public static bool Update(LoaiPhongDTO loaiPhongdto)
        {
            string sql = "UPDATE LoaiPhong SET tenLoaiPhong=N'" + loaiPhongdto.TenLoaiPhong + "',donGia=" + loaiPhongdto.DonGia + " WHERE maLoaiPhong=" + loaiPhongdto.MaLoaiPhong;
            DataAccessHelper s = new SqlDataAccessHelper();

            int kq = s.ExecuteNonQuery1(sql);
            if (kq < 0)
                return false;
            return true;
        }
        
        // Lay tat ca danh sach loai phong
        public static DataTable LayDanhSachLoaiPhong()
        {
            DataTable dt = new DataTable();
            string sql = "Select MaLoaiPhong=maLoaiPhong,TenLoaiPhong=tenLoaiPhong,DonGia=donGia from LoaiPhong where danhDau=0 ";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LayDanhSachLoaiPhong1()
        {
            DataTable dt = new DataTable();
            string sql = "Select MaLoaiPhong=maLoaiPhong,TenLoaiPhong=tenLoaiPhong,DonGia=donGia from LoaiPhong where danhDau=0 ";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }
        
        // Lay danh sach loai phong theo ten loai phong
        public static DataTable LayDanhSachLoaiPhongTheoTenLoaiPhong(string tenLoai)
        {
            DataTable dt = new DataTable();
            string sql = "Select MaLoaiPhong=maLoaiPhong,TenLoaiPhong=tenLoaiPhong,DonGia=donGia from LoaiPhong where tenLoaiPhong=N'" + tenLoai + "' and danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LayMaPhongTheoTen(string tenLoaiPhong)
        {
            DataTable dt = new DataTable();
            string sql = "SELECT * FROM LoaiPhong WHERE danhDau=0 and tenLoaiPhong=N'" + tenLoaiPhong.ToString() + "'";
            DataAccessHelper cn = new SqlDataAccessHelper();
            dt = cn.ExecuteQuery(sql);
            return dt;
        }
        
        // Lay thong tin loai phong theo don gia
        public static DataTable  LayLoaiPhongTheoDonGia(string donGia)
        {
            DataTable dt = new DataTable();
            float donGiaLoaiPhong = float.Parse(donGia);
            string sql = "SELECT * FROM LoaiPhong WHERE donGia=" + donGiaLoaiPhong + "and danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }
        public static LoaiPhongDTO LayLoaiPhongTheoMa(int maLoai)
        {
            DataTable dt = new DataTable();
            //float donGiaLoaiPhong = float.Parse(donGia);
            string sql = "SELECT * FROM LoaiPhong WHERE maLoaiPhong=" + maLoai.ToString() + "and danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            LoaiPhongDTO lp = new LoaiPhongDTO();
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                lp.MaLoaiPhong = maLoai;
                lp.TenLoaiPhong = dr["tenLoaiPhong"].ToString();
                lp.DonGia = float.Parse(dr["donGia"].ToString());
            }
            return lp;
        }
        //tong doanh thu thang
        public static float TongDoanhThuThang(int thang, int nam)
        {
            Dictionary<string, object> para = new Dictionary<string, object>();
            List<ParameterDirection> paraDir = new List<ParameterDirection>();
            DataAccessHelper help = new SqlDataAccessHelper();

            float kq = 0;
            para.Add("thang", thang);
            paraDir.Add(ParameterDirection.Input);
            para.Add("nam", nam);
            paraDir.Add(ParameterDirection.Input);
            para.Add("tongDT", kq);
            paraDir.Add(ParameterDirection.Output);

            List<string> strkq = help.ExecuteNonProcedure("sp_TongDoanhThuThang", para, paraDir);
            kq = float.Parse(strkq[2]);

            return kq;
        }
        //doanh thu theo loai
        public static float DoanhThuTheoLoai(int thang, int nam, int maLoai)
        {
            Dictionary<string, object> para = new Dictionary<string, object>();
            List<ParameterDirection> paraDir = new List<ParameterDirection>();
            DataAccessHelper help = new SqlDataAccessHelper();

            float kq = 0;
            para.Add("thang", thang);
            paraDir.Add(ParameterDirection.Input);
            para.Add("nam", nam);
            paraDir.Add(ParameterDirection.Input);
            para.Add("loaiPhong", maLoai);
            paraDir.Add(ParameterDirection.Input);
            para.Add("tongDT", kq);
            paraDir.Add(ParameterDirection.Output);

            List<string> strkq = help.ExecuteNonProcedure("sp_TongDoanhThuThangTheoLoaiPhong", para, paraDir);
            kq = float.Parse(strkq[3]);

            return kq;
        }
    }
}
